package com.suoluo.zenadmin.dao.dest;

import java.util.Date;
import java.util.List;
import java.util.Map;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.ReturnGeneratedKeys;
import net.paoding.rose.jade.annotation.SQL;
import net.paoding.rose.jade.annotation.SQLParam;

import com.suoluo.zenadmin.vo.country.CountryVO;
import com.suoluo.zenadmin.vo.dest.DestActivityVO;
import com.suoluo.zenadmin.vo.dest.DestFeature2VO;
import com.suoluo.zenadmin.vo.dest.DestFeatureCategoryVO;
import com.suoluo.zenadmin.vo.dest.DestFeatureItem2VO;
import com.suoluo.zenadmin.vo.dest.DestPathVO;
import com.suoluo.zenadmin.vo.dest.DestVO;
import com.suoluo.zenadmin.vo.dest.SelectFeatureItemVO;
import com.suoluo.zenadmin.vo.hotel.Hotel2VO;

@DAO
public interface DestDAO {

	@SQL("select * from tb_dest t1 join tb_image t2 on t1.image_id = t2.image_id")
	public List<DestVO> findDestsWithoutContent();

	@SQL("select * from tb_dest_activity where t_dest_id=:1")
	public List<DestActivityVO> findDestActivity(int destId);

	@SQL("insert into tb_dest_activity(t_activity_title,t_activity_address,"
			+ "t_activity_img,t_activity_desc,t_activity_start,t_activity_end,t_dest_id) "
			+ "values(:1.activityTitle,:1.activityAddress,:1.activityImg,:1.activityDesc,:1.activityStart,:1.activityEnd,:1.destId)")
	public void saveDestActivity(DestActivityVO activity);

	@SQL("select * from tb_dest_activity where t_activity_id=:1")
	public DestActivityVO findDestActivityById(int activityId);

	@SQL("update tb_dest_activity set t_activity_title=:1.activityTitle,t_activity_address=:1.activityAddress,"
			+ "t_activity_img=:1.activityImg,t_activity_desc=:1.activityDesc,"
			+ "t_activity_start=:1.activityStart,"
			+ "t_activity_end=:1.activityEnd, t_dest_id=:1.destId where t_activity_id=:1.activityId")
	public void updateActivity(DestActivityVO activity);

	@SQL("delete from tb_dest_activity where t_activity_id=:1")
	public void deleteDestActivity(int activityId);

	@SQL("select * from tb_dest_feature_item2 where feature_item_id=:1")
	public DestFeatureItem2VO findSimplestFeatureItemById(int id);

	@SuppressWarnings("rawtypes")
	@SQL("select td.feature_id featureId,td.feature_item_id featureItemId,"
			+ "td.zhname,t1.source  from tb_select_feature_item  ts "
			+ "left  join tb_dest_feature_item2 td on ts.t_feature_item_id = td.feature_item_id "
			+ "join tb_image t1 on t1.image_id = td.image_id "
			+ "where td.feature_id =:1 order by featureItemId desc")
	public List<Map> findListFeatureSelectItem(int featureId);

	@SQL("select count(t_feature_id) from tb_select_feature_item where t_feature_id=:1")
	public int findSelectFeatureItemCount(int featureId);

	@SQL("insert into tb_select_feature_item(t_feature_id,t_feature_item_id) "
			+ "values(:1.featureId,:1.featureItemId)")
	public void saveSelectFeatureItem(SelectFeatureItemVO itemVO);

	@SQL("delete from tb_select_feature_item where t_select_item_id=:1")
	public void deleteSelectFeatureItem(int selectItemId);

	// @SQL("delete from tb_select_feature_item where t_feature_item_id=:1")
	// public void deleteSelectItemByItemId(int itemId);

	@SuppressWarnings("rawtypes")
	@SQL("select td.feature_id featureId,td.feature_item_id featureItemId,"
			+ "td.zhname,t1.source,if(isnull(ts.t_feature_item_id),0,1) link,"
			+ "ts.t_select_item_id selectItemId from tb_dest_feature_item2 td "
			+ "left  join  tb_select_feature_item  ts on ts.t_feature_item_id = "
			+ "td.feature_item_id join tb_image t1 on t1.image_id = td.image_id "
			+ " where td.feature_id =:1 and td.deleted = 0 order by featureItemId desc")
	public List<Map> findListAllFeatureItem(int featureId);

	@SuppressWarnings("rawtypes")
	@SQL("select ct.t_continent_id,ct.t_continent_name, cy.t_country_id,cy.t_country_name,td.dest_id,td.zhname,td.enname, "
			+ "td.lng,td.lat,i.source,i.small from tb_continent ct "
			+ "left join tb_country cy on ct.t_continent_id = cy.t_continent_id "
			+ "left join tb_dest td on cy.t_country_id = td.country_id "
			+ "join tb_image i on i.image_id = td.image_id "
			+ "where td.dest_enable != 0")
	public List<Map> findDestNavigateTree();

	@SQL("select ct.t_continent_name continentName,cy.t_country_name countryName,d.zhname,d.enname "
			+ "from tb_dest d "
			+ "join tb_country cy on d.country_id = cy.t_country_id "
			+ "join tb_continent ct on cy.t_continent_id = ct.t_continent_id "
			+ "where d.dest_id = :1")
	public DestPathVO findDestPath(int destId);

	@SQL("select * from tb_dest_feature_item2 t1 join tb_image t2 on t1.image_id = t2.image_id where t1.feature_item_id <> :1 and t1.feature_id = :2 limit 0,:3")
	public List<DestFeatureItem2VO> findSamilarDestFeatureItems(int itemId,
			int featureId, int pageSize);

	@SQL("select * from tb_dest_feature2 d join tb_image i on d.image_id = i.image_id where d.dest_id = :1 order by feature_order asc")
	public List<DestFeature2VO> findDestFeaturesWithoutContentByDestId(
			int destId);

	@SQL("select dfi.* from tb_dest_feature2 df join tb_select_feature_item sfi on df.feature_id = sfi.t_feature_id "
			+ "join tb_dest_feature_item2 dfi on sfi.t_feature_item_id = dfi.feature_item_id where df.dest_id = :1")
	public List<DestFeatureItem2VO> findSelectedSimplestDestFeatureItemsByDestId(
			int destId);

	@SQL("select * from tb_dest_feature_item2 fi join tb_image i on fi.image_id = i.image_id where fi.feature_id = :1 and fi.deleted = 0 order by fi.feature_item_id desc")
	public List<DestFeatureItem2VO> findFeatureItemsWithoutContentByFeatureId(
			int featureId);

	// @SQL("select tf.feature_id featureId,tc.t_feature_category_id categoryId,"
	// + "tc.t_feature_category_name categoryName,"
	// + "tc.t_feature_category_order categoryOrder "
	// + "from tb_dest_feature2 tf join  tb_dest_feature_category tc "
	// + "on tf.feature_category_id = tc.t_feature_category_id "
	// + "where tf.dest_id =:1 order by categoryOrder")
	// public List<TripFeatureVO> findListFeatureByDestId(int destId);

	@SuppressWarnings("rawtypes")
	@SQL("select ct.t_continent_id,ct.t_continent_name, cy.t_country_id,cy.t_country_name,td.dest_id,td.zhname,td.enname,td.lat,td.lng "
			+ "from tb_continent ct "
			+ "left join tb_country cy on ct.t_continent_id = cy.t_continent_id "
			+ "left join tb_dest td on cy.t_country_id = td.country_id "
			+ "where td.dest_enable = 1 or (td.dest_enable = 0 and td.hotel_enable = 1)")
	public List<Map> findHotelNavigateTree();

	@SQL("select * from tb_dest_feature2 where feature_id= :1")
	public DestFeature2VO findSimplestFeatureById(int featureId);

	@SQL("select * from tb_dest t1 join tb_base_info t2 "
			+ "on t2.data_id=t1.dest_id and info_type=1 where 1=1 "
			+ "#if(:destName != null){and t1.zhname like :destName} "
			+ " order by dest_id desc limit :start,:rows")
	public List<DestVO> findSimplestDests(
			@SQLParam("destName") String destName,
			@SQLParam("start") int start, @SQLParam("rows") int rows);

	@SQL("select count(dest_id) from tb_dest  where 1=1 "
			+ "#if(:destName != null){and zhname like :destName}")
	public int findDestCount(@SQLParam("destName") String destName);

	@SQL("select * from tb_country")
	public List<CountryVO> findCountries();

	@ReturnGeneratedKeys
	@SQL("insert into tb_dest(zhname,country_id,country_name,altitude,people_num,lang,tel,currency,"
			+ "lat,lng,summary,dest_enable,hotel_enable,url,aviation,railroad,highway,"
			+ "water_transport,local_transport,enname,style,copyright) values(:1.zhname,:1.countryId,:1.countryName,:1.altitude,"
			+ ":1.peopleNum,:1.lang,:1.tel,:1.currency,:1.lat,:1.lng,:1.summary,:1.destEnable,"
			+ ":1.hotelEnable,:1.url,:1.aviation,:1.railroad,:1.highway,:1.waterTransport,"
			+ ":1.localTransport,:1.enname,:1.style,:1.copyright)")
	public int saveDest(DestVO destVO);

	@SQL("update tb_dest set image_id = :2 where dest_id = :1")
	public void updateDestImageId(int destId, int imageId);

	@SQL("select * from tb_dest t1 join tb_base_info t2 on t2.data_id=t1.dest_id "
			+ "and t2.info_type=:2 join tb_image t3 on t3.image_id = t1.image_id "
			+ "where t1.dest_id = :1")
	public DestVO findDestById(int destId, int infoType);

	@SQL("select * from tb_dest where dest_id = :1")
	public DestVO findSimplestDestById(int destId);

	@SQL("select * from tb_dest t1 join tb_image t2 on t1.image_id = t2.image_id where t1.dest_id = :1")
	public DestVO findDestWithoutContentById(int destId);

	@SQL("update tb_dest set zhname=:1.zhname,altitude=:1.altitude,people_num=:1.peopleNum,"
			+ "lang=:1.lang,tel=:1.tel,currency=:1.currency,lat=:1.lat,lng=:1.lng,"
			+ "summary=:1.summary,dest_enable=:1.destEnable,hotel_enable=:1.hotelEnable,"
			+ "url=:1.url,aviation=:1.aviation,railroad=:1.railroad,highway=:1.highway,"
			+ "water_transport=:1.waterTransport,local_transport=:1.localTransport,"
			+ "enname=:1.enname,style=:1.style,copyright=:1.copyright where dest_id=:1.destId")
	public void updateDest(DestVO destVO);

	@SQL("select sum(num) from ("
			+ "select count(dest_id) num from tb_dest_feature2 where dest_id = :destId "
			+ "UNION all select count(b.dest_id) num from tb_dest_traffic2 b where b.dest_id = :destId  "
			+ "UNION all select count(c.dest_id) num from tb_hotel2 c where c.dest_id = :destId  "
			+ "UNION all select count(d.t_item_id) num from tb_home_item d where d.t_category_id = 2 and d.t_dest_id = :destId  "
			+ " ) aa")
	public int findDestForginKeyCount(@SQLParam("destId") int destId);

	@SQL("delete from tb_dest where dest_id = :1 ")
	public void deleteDest(int destId);

	@SQL("select * from tb_dest_feature2 where dest_id = :1")
	public List<DestFeature2VO> findSimpleDestFeatures(int destId);

	@ReturnGeneratedKeys
	@SQL("insert into tb_dest_feature2(zhname,dest_id,feature_category_id,feature_order,"
			+ "feature_item_count,feature_tip,summary,copyright) values(:1.zhname,:1.destId,"
			+ ":1.featureCategoryId,:1.featureOrder,:1.featureItemCount,"
			+ ":1.featureTip,:1.summary,:1.copyright)")
	public int saveDestFeature(DestFeature2VO feature);

	@SQL("update tb_dest_feature2 set image_id=:2 where feature_id =:1")
	public void updateFeatureImageId(int featureId, int imageId);

	@SQL("select * from tb_dest_feature2 t1 join tb_base_info t2 on t2.data_id=t1.feature_id "
			+ "and t2.info_type=:2 join tb_image t3 on t3.image_id = t1.image_id "
			+ "where t1.feature_id = :1")
	public DestFeature2VO findDestFeatureById(int featureId, int infoType);

	@SQL("select count(dfi.feature_item_id) from tb_home_category hc "
			+ "join tb_home_item hi on hc.t_category_id = hi.t_category_id "
			+ "join tb_dest_feature_item2 dfi on dfi.feature_item_id = hi.t_link_id "
			+ "where hc.t_feature_category_id = :2 and dfi.feature_id=:1;")
	public Integer findDestFeatureInHomeCategoryCount(int featureId,
			int categoryId);

	@SQL("update tb_dest_feature2 set feature_category_id=:1.featureCategoryId,"
			+ "zhname=:1.zhname,feature_order=:1.featureOrder,"
			+ "feature_item_count=:1.featureItemCount,copyright=:1.copyright, "
			+ "feature_tip=:1.featureTip,summary=:1.summary where feature_id=:1.featureId")
	public void updateDestFeature(DestFeature2VO feature);

	@SQL("select count(feature_id) from tb_dest_feature_item2 where feature_id = :1")
	public int findFeatureForeignKeyCount(int featureId);

	@SQL("delete from tb_dest_feature2 where feature_id = :1")
	public void deleteDestFeature(int featureId);

	@SQL("select * from tb_dest_feature_item2 where feature_id= :1 order by feature_item_id desc")
	public List<DestFeatureItem2VO> findSimplestFeatureItems(int featureId);

	@ReturnGeneratedKeys
	@SQL("insert  into tb_dest_feature_item2(zhname,enname,feature_id,create_time,lat,lng,"
			+ "feature_category_id,stay_time,summary,tel,url,address,price,logo,email,note,"
			+ "open_time,menu,rel_id,property_visible,dest_id,copyright,expire_time) values(:1.zhname,"
			+ ":1.enname,:1.featureId,:1.createTime,:1.lat,:1.lng,:1.featureCategoryId,:1.stayTime,"
			+ ":1.summary,:1.tel,:1.url,:1.address,:1.price,:1.logo,:1.email,:1.note,"
			+ ":1.openTime,:1.menu,:1.relId,:1.propertyVisible,:1.destId,:1.copyright,:1.expireTime)")
	public int saveDestFeatureItem(DestFeatureItem2VO itemVO);

	@SQL("update tb_dest_feature_item2 set image_id=:2 where feature_item_id=:1")
	public void updateDestFeatureItemImageId(int itemId, int imageId);

	@SQL("update tb_dest_feature_item2 set zhname=:1.zhname,enname=:1.enname,"
			+ "feature_id=:1.featureId,expire_time=:1.expireTime,deleted=:1.deleted,"
			+ "create_time=:1.createTime,lat=:1.lat,lng=:1.lng,summary=:1.summary,"
			+ "feature_category_id=:1.featureCategoryId,stay_time=:1.stayTime,tel=:1.tel,"
			+ "url=:1.url,address=:1.address,price=:1.price,logo=:1.logo,email=:1.email,"
			+ "note=:1.note,open_time=:1.openTime,menu=:1.menu,copyright=:1.copyright,"
			+ "property_visible=:1.propertyVisible where feature_item_id=:1.featureItemId")
	public void updateDestFeatureItem(DestFeatureItem2VO item);

	@SQL("select * from tb_dest_feature_item2 t1 join tb_base_info t2 on t2.data_id=t1.feature_item_id "
			+ "and t2.info_type=:2 join tb_image t3 on t3.image_id = t1.image_id "
			+ "where t1.feature_item_id = :1 and t2.default_info=1")
	public DestFeatureItem2VO findFeatureItemById(int featureItemId,
			int infoType);

	@SQL("delete from tb_select_feature_item where t_feature_item_id=:1")
	public void deleteSelectItemByItemId(int itemId);

	@SQL("delete from tb_dest_feature_item2 where feature_item_id =:1")
	public void deleteDestFeatureItem(int itemId);

	@SQL("insert  into tb_dest_feature_item2(zhname,enname,feature_id,create_time,lat,lng,"
			+ "feature_category_id,stay_time,summary,tel,url,address,price,logo,email,note,"
			+ "open_time,menu,rel_id,property_visible,image_id,mobile_image_id,dest_id) values(:1.zhname,:1.enname,"
			+ ":1.featureId,:1.createTime,:1.lat,:1.lng,:1.featureCategoryId,:1.stayTime,"
			+ ":1.summary,:1.tel,:1.url,:1.address,:1.price,:1.logo,:1.email,:1.note,"
			+ ":1.openTime,:1.menu,:1.relId,:1.propertyVisible,:1.imageId,:1.mobileImageId,:1.destId)")
	public int saveHotelFeatureItem(List<DestFeatureItem2VO> itemVO);

	@SQL("select th.hotel_id,th.zhname,th.enname,th.summary,"
			+ "th.lat,th.lng,th.logo,th.address,th.tel,th.price,"
			+ "th.email,th.image_id,th.mobile_image_id "
			+ " from tb_hotel2 th " + "where dest_id= :1 and hotel_id not in "
			+ "(select rel_id from tb_dest_feature_item2 where feature_id =:2)")
	public List<Hotel2VO> findListDestHotel(int destId, int featureId);

	@SQL("select * from tb_dest_feature_item2 where dest_id=0 order by feature_item_id desc limit :1,:2")
	public List<DestFeatureItem2VO> findListAloneFeatureItem(int startPage,
			int rows);

	@SQL("select * from tb_dest_feature_item2 t1 join tb_image t2 on t1.image_id = t2.image_id where t1.dest_id = :1 and t1.deleted = 0 "
			+ "#if(:2 >0 ){ and t1.feature_category_id =:2 }")
	public List<DestFeatureItem2VO> findFeatureItemsWithoutContent(int destId,
			int categoryId);

	@SQL("select t2.* from tb_dest_feature2 t1 join tb_dest_feature_category t2 on t1.feature_category_id = t2.t_feature_category_id where t1.dest_id = :1")
	public List<DestFeatureCategoryVO> findDestFeatureCatetories(int destId);

	@SQL("select count(*) from tb_dest_feature2 where dest_id=:1 and feature_category_id=:2")
	public int findDestFeatureIsAdd(int destId, int categoryId);

	@SQL("select count(*) from tb_dest_feature_item2 where dest_id=0")
	public int findAloneFeatureItemCount();

	@SQL("update tb_dest_feature_item2 set feature_category_id =:1.featureCategoryId "
			+ " where feature_id =:1.featureId ")
	public void updateDestFeatureItemCategoryId(DestFeature2VO feature);

	@SQL("select * from tb_dest_feature_item2 where expire_time < :1 and expire_time != 0 limit :2,:3")
	public List<DestFeatureItem2VO> findListExpireFeatureItem(Date date,
			int start, int rows);

	@SQL("select count(feature_item_id) from tb_dest_feature_item2 where expire_time < :1 and expire_time != 0")
	public int findExpireFeatureItemCount(Date date);

	@SQL("update tb_dest_feature_item2 set deleted=1 where feature_item_id=:1")
	public void updateFeatureItemDeleted(int featureItemId);

	// @SQL("update tb_dest_feature_item2 set expire_time=:1.expiretume, " +
	// "create_time=:1.createTime where feature_item_id=:1.featureItemId")
	// public void updateExpireFeatureItem(DestFeatureItem2VO item);

	@SQL("select * from tb_dest_feature_item2 t1 join tb_image t2 on t2.image_id = t1.image_id where t1.dest_id=:1 "
			+ "and t1.lat is not null and t1.lng is not null "
			+ "and t1.feature_category_id != 5 and t1.rel_id = 0 limit 0,4")
	public List<DestFeatureItem2VO> find4FeatureItems(int destId);
	
	@SQL("select * from tb_dest")
	public List<DestVO> findAllDest();
	
	@SQL("select * from tb_dest_feature2 d join tb_image i on d.image_id = i.image_id " +
			"join tb_base_info tb on tb.data_id = d.feature_id and tb.info_type=:2 " +
			"where d.dest_id = :1 order by d.feature_order asc")
	public List<DestFeature2VO> findDestFeatures(int destId,int infoType);
	
	@SQL("select * from tb_dest_feature_item2 d join tb_image i on i.image_id = d.image_id " +
		"where d.feature_id in (:1) and d.deleted = 0")
	public List<DestFeatureItem2VO> findFeatureItems(List<Integer> featureIds);
}
